﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Summary description for PostingDAO
/// </summary>
public class PostingDAO:DBAccess,IPosting
{
    #region sql commands
    private string SQL_GET_ALL = "select * from Postings";
    private string SQL_GET_ALL_BY_COMPETID = "select * from Postings Where CompetitionID = @competitionID";
    private string SQL_SELECT_BY_ID = "select * from Postings where PostingID = @postingID";
    private string SQL_INSERT_POSTING = "insert into Postings(PostingName, PostDate, Description, Price, StudentID, CompetitionID, Count, Url, Quotations) values(@postingName, @postDate, @description, @price, @studentID, @competitionID, @count, @url, @quotations)";
    private string SQL_UPDATE_POSTING = "update Postings set PostingName = @postingName, PostDate = @postDate, Description = @description, Choosen = @choosen, Price = @price, StudentID = @studentID, CompetitionID = @competitionID, Count = @count, Url = @url, Quotations = @quotations where PostingID = @postingID";
    private string SQL_STUDENT_UPDATE_POSTING = "update Postings set PostingName = @postingName, PostDate = @postDate, Description = @description, Price = @price, CompetitionID = @competitionID, Url = @url, Quotations = @quotations where PostingID = @postingID";
    private string SQL_DELETE_POSTING = "delete from Postings where PostingID = @postingID";
    private string SQL_DELETE_POSTING_BY_COMPETID = "delete from Postings where CompetitionID = @competitionID";
    private string SQL_SELECT_BEST_POSTING = "select p.PostingID, p.PostingName, p.Description, p.Url, p.Quotations, p.PostDate, ui.FullName, ui.Class" 
        +"from Postings as p join UserInfomations as ui on p.StudentID = ui.UserID";
    private string SQL_SELECT_POSTING_BY_COMPETITION = "select p.PostingID, p.PostingName, p.Description, p.Url, p.Quotations, c.CompetitionID, c.CompetitionName, c.StartDate, c.EndDate"
         + "from Postings as p join Competitions as c on p.CompetitionID = c.CompetitionID where p.CompetitionID = @competitionId";

    private string SQL_SELECT_POSTING_BY_POSTINGID = "select p.PostingID, p.Price,p.PostingName,p.PostDate, p.Description, p.Choosen, p.Url, p.Quotations, u.Fullname, u.Birthday, u.Class, u.Email from Postings as p join UserInfomations as u on p.StudentID = u.UserID where PostingID = @postingID";
    private string SQL_SELECT_POSTINGINFO_BY_COMPETITIONID = "select p.StudentID, p.PostingID, p.PostingName, p.Url, u.Fullname, p.Quotations, p.PostDate from Postings as p join UserInfomations as u on p.StudentID = u.UserID where p.CompetitionID = @competitionID";
    //private string SQL_SELECT_POSTINGINFO_BY_COMPETITIONID = "select p.PostingID, p.PostingName, u.Fullname, p.PostDate, p.Url from Postings as p join UserInfomations as u on p.StudentID = u.UserID where p.CompetitionID = @competitionID";

    private string SQL_SELECT_POSTINGINFO_BY_STUDENTID = "select p.PostingID, p.PostingName, p.Url, p.Quotations, c.CompetitionName, p.PostDate from Postings as p join Competitions as c on p.CompetitionID = c.CompetitionID where studentID = @studentID";
    private string SQL_SELECT_MARKDETAIL_BY_POSTINGID = "select u.Fullname, u.Email, m.Mark, m.Remarks from Postings as p join Marks as m on p.PostingID = m.PostingID join UserInfomations as u on m.StaffID = u.UserID where p.PostingID = @PostingID";
    private string SQL_SELECT_MARK_BY_POSTINGID = "select Mark from Marks where PostingID = @PostingID";

    private string SQL_SELECT_POSTING_IN_EXHIBITION = "select p.PostingID, p.PostingName, p.Description, p.Price, p.Url, p.Quotations, u.Fullname, u.Phone, pe.Status from Postings as p join UserInfomations as u on p.StudentId = u.UserID join PostingExhibition as pe" +
              " on p.PostingID = pe.PostingID join Exhibition as e on pe.ExhibitionID = e.ExhibitionID where e.ExhibitionID = @exhibitionId";
    private string SQL_SELECT_POSTING_BY_ID = "select p.PostingID, p.PostingName, p.Description, p.Price, p.Url, p.Quotations, u.Fullname, u.Phone, pe.Status from Postings as p join UserInfomations as u on p.StudentId = u.UserID join PostingExhibition as pe" +
              " on p.PostingID = pe.PostingID join Exhibition as e on pe.ExhibitionID = e.ExhibitionID where pe.PostingID = @postingId";

    private string SQL_COUNT_POSTING_BY_COMPETITIONID = "select count(PostingID) from Postings Where CompetitionID = @compID ";
    
    public string message;

    #endregion

	public PostingDAO()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    #region IPosting Members
    /// <summary>
    /// Get All Posting
    /// </summary>
    /// <returns>IList</returns>
    public IList<PostingInfo> GetAllPosting()
    {
        IList<PostingInfo> postingList = new List<PostingInfo>();
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_GET_ALL, conn);
            if (Connect())
            {
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    PostingInfo post = new PostingInfo();
                    post.PostingId = reader.GetInt64(0);
                    post.PostingName = reader.GetString(1);
                    post.PostDate = reader.GetDateTime(2);
                    post.Description = reader.GetString(3);
                    if(!reader.IsDBNull(4))
                    post.Choosen = reader.GetBoolean(4);
                    post.Price = reader.GetDouble(5);
                    post.StudentId = reader.GetString(6);
                    post.CompetitionId = reader.GetInt32(7);
                    post.Count = reader.GetInt64(8);
                    post.Url = reader.GetString(9);
                    post.Quotations = reader.GetString(10);

                    postingList.Add(post);
                }
                Disconnect();
            }
        }
       catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return postingList;
    }

    /// <summary>
    /// Get All Posting of Competition
    /// </summary>
    /// <returns>IList</returns>
    public IList<PostingInfo> GetAllPostingByCompetID(int competID)
    {
        IList<PostingInfo> postingList = new List<PostingInfo>();
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_GET_ALL_BY_COMPETID, conn);
            cmd.Parameters.AddWithValue("@competitionID", competID);
            if (Connect())
            {
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    PostingInfo post = new PostingInfo();
                    post.PostingId = reader.GetInt64(0);
                    post.PostingName = reader.GetString(1);
                    post.PostDate = reader.GetDateTime(2);
                    post.Description = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        post.Choosen = reader.GetBoolean(4);
                    post.Price = reader.GetDouble(5);
                    post.StudentId = reader.GetString(6);
                    post.CompetitionId = reader.GetInt32(7);
                    post.Count = reader.GetInt64(8);
                    post.Url = reader.GetString(9);
                    post.Quotations = reader.GetString(10);

                    postingList.Add(post);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return postingList;
    }

    /// <summary>
    /// Get Posting by ID
    /// </summary>
    /// <returns>PostingInfo</returns>
    public PostingInfo GetPostingById(Int64 postingId)
    {
        PostingInfo post = new PostingInfo();
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_SELECT_BY_ID, conn);
            cmd.Parameters.AddWithValue("@postingID", postingId);
            if (Connect())
            {
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    post.PostingId = reader.GetInt64(0);
                    post.PostingName = reader.GetString(1);
                    post.PostDate = reader.GetDateTime(2);
                    post.Description = reader.GetString(3);
                    if(!reader.IsDBNull(4))
                    post.Choosen = reader.GetBoolean(4);
                    post.Price = reader.GetDouble(5);
                    post.StudentId = reader.GetString(6);
                    post.CompetitionId = reader.GetInt32(7);
                    post.Count = reader.GetInt64(8);
                    post.Url = reader.GetString(9);
                    post.Quotations = reader.GetString(10);
                   
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return post;
    }

    /// <summary>
    /// InsertPosting
    /// </summary>
    /// <returns>int</returns>
    public int InsertPosting(PostingInfo post)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_INSERT_POSTING, conn);
            cmd.Parameters.AddWithValue("@postingName", post.PostingName);
            cmd.Parameters.AddWithValue("@postDate", post.PostDate);
            cmd.Parameters.AddWithValue("@description", post.Description);
            cmd.Parameters.AddWithValue("@price", post.Price);
            cmd.Parameters.AddWithValue("@studentID", post.StudentId);
            cmd.Parameters.AddWithValue("@competitionID", post.CompetitionId);
            cmd.Parameters.AddWithValue("@count", post.Count);
            cmd.Parameters.AddWithValue("@url", post.Url);
            cmd.Parameters.AddWithValue("@quotations", post.Quotations);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }
    /// <summary>
    /// Update Posting
    /// </summary>
    /// <returns>int</returns>
    public int UpdatePosting(PostingInfo post)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_UPDATE_POSTING, conn);
            cmd.Parameters.AddWithValue("@postingName", post.PostingName);
            cmd.Parameters.AddWithValue("@postDate", post.PostDate);
            cmd.Parameters.AddWithValue("@description", post.Description);
            cmd.Parameters.AddWithValue("@choosen", post.Choosen);
            cmd.Parameters.AddWithValue("@price", post.Price);
            cmd.Parameters.AddWithValue("@studentID", post.StudentId);
            cmd.Parameters.AddWithValue("@competitionID", post.CompetitionId);
            cmd.Parameters.AddWithValue("@count", post.Count);
            cmd.Parameters.AddWithValue("@url", post.Url);
            cmd.Parameters.AddWithValue("@quotations", post.Quotations);
            cmd.Parameters.AddWithValue("@postingID", post.PostingId);

            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }
    /// <summary>
    /// Student update Posting
    /// </summary>
    /// <returns>int</returns>
    public int StudentUpdatePosting(PostingInfo post)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_STUDENT_UPDATE_POSTING, conn);
            cmd.Parameters.AddWithValue("@postingName", post.PostingName);
            cmd.Parameters.AddWithValue("@postDate", post.PostDate);
            cmd.Parameters.AddWithValue("@description", post.Description);
            cmd.Parameters.AddWithValue("@price", post.Price);
            cmd.Parameters.AddWithValue("@competitionID", post.CompetitionId);
            cmd.Parameters.AddWithValue("@url", post.Url);
            cmd.Parameters.AddWithValue("@quotations", post.Quotations);
            cmd.Parameters.AddWithValue("@postingID", post.PostingId);

            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }


    /// <summary>
    /// Delete Posting
    /// </summary>
    /// <returns>int</returns>
    public int DeletePosting(Int64 postingId)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_DELETE_POSTING, conn);
            cmd.Parameters.AddWithValue("@postingID", postingId);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }

    /// <summary>
    ///  Delete Posting by CompetionID
    /// </summary>
    /// <returns>IList</returns>
    public int DeletePostingByCompetID(int competID)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_DELETE_POSTING_BY_COMPETID, conn);
            cmd.Parameters.AddWithValue("@competitionID", competID);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }

   


    /// <summary>
    /// Get Best Posting
    /// </summary>
    /// <returns>DataSet</returns>
    public DataSet GetBestPosting()
    {
        throw new NotImplementedException();
    }

    /// <summary>
    /// Get Postings had done based competitions name
    /// </summary>
    /// <param name="competitionName"></param>
    /// <returns>Dataset</returns>
    //public DataSet GetPostingByCompetitionID(int competitionId)
    //{
    //    DataSet ds = new DataSet();
    //    SqlDataAdapter adapter = new SqlDataAdapter(SQL_SELECT_POSTING_BY_COMPETITION, conn);
    //    adapter.SelectCommand.Parameters.AddWithValue("@competitionId", competitionId);
    //    adapter.Fill(ds, "PostingCompeted");
    //    return ds;
    //}

  
    /// <summary>
    /// Get Posting Information by competition Id
    /// </summary>
    /// <param name="competitionId"></param>
    /// <returns>Dataset</returns>
    public DataSet GetPostingInfoByCompetitionID(int competitionId)
    {
        DataSet ds = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter(SQL_SELECT_POSTINGINFO_BY_COMPETITIONID, conn);
        adapter.SelectCommand.Parameters.AddWithValue("@competitionId", competitionId);
        adapter.Fill(ds);
        return ds;
    }


    /// <summary>
    /// Get Posting Information by student Id
    /// </summary>
    /// <param name="competitionId"></param>
    /// <returns>Dataset</returns>
    public DataSet GetPostingInfoByStudentID(string studentID)
    {
        DataSet ds = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter(SQL_SELECT_POSTINGINFO_BY_STUDENTID, conn);
        adapter.SelectCommand.Parameters.AddWithValue("@studentID", studentID);
        adapter.Fill(ds);
        return ds;
    }

    /// <summary>
    /// Get PostingInfo
    /// </summary>
    /// <returns>DataSet</returns>
    public DataSet GetPostingInfoByPostingID(Int64 postingID)
    {
        DataSet ds = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter(SQL_SELECT_POSTING_BY_POSTINGID, conn);
        adapter.SelectCommand.Parameters.AddWithValue("@postingID", postingID);
        adapter.Fill(ds);
        return ds;
    }


    /// <summary>
    /// Get Mark of Posting
    /// </summary>
    /// <returns>DataSet</returns>
    public DataSet getMarkByPostingID(Int64 postingID)
    {
        DataSet ds = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter(SQL_SELECT_MARKDETAIL_BY_POSTINGID, conn);
        adapter.SelectCommand.Parameters.AddWithValue("@PostingID", postingID);
        adapter.Fill(ds);
        return ds;
    }

    /// <summary>
    /// Get Posting in Exhibition
    /// </summary>
    /// <returns>DataSet</returns>
    public DataSet GetPostingInExhibition(int exhibitionId)
    {
        DataSet ds = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter(SQL_SELECT_POSTING_IN_EXHIBITION, conn);
        adapter.SelectCommand.Parameters.AddWithValue("@exhibitionId", exhibitionId);
        adapter.Fill(ds);
        return ds;
    }
    /// <summary>
    /// Get Mark of Posting
    /// </summary>
    /// <returns>IList</returns>
    public IList<string> GetMark(Int64 postingID)
    {
        IList<string> list = new List<string>();
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_SELECT_MARK_BY_POSTINGID, conn);
            cmd.Parameters.Add("@PostingID", postingID);
            if (Connect())
            {
                SqlDataReader reader = cmd.ExecuteReader();
                
                while (reader.Read())
                {
                    list.Add(reader.GetString(0));
                    
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }


    /// <summary>
    /// Average Mark of Posting
    /// </summary>
    /// <returns>IList</returns>
    public string Average(Int64 postID)
    {
        string average = "";
        IList<string> listmarks = GetMark(postID);
        if (listmarks.Count > 0)
        {
            int marknum = 0;
            foreach (string mark in listmarks)
            {
                int temp = 0;
                if (mark.Equals("Best"))
                    temp = 5;
                else if (mark.Equals("Better"))
                    temp = 4;
                else if (mark.Equals("Good"))
                    temp = 3;
                else if (mark.Equals("Moderate"))
                    temp = 2;
                else if (mark.Equals("Normal"))
                    temp = 1;
                else if (mark.Equals("Disqualified"))
                    marknum = 0;
                marknum = marknum + temp;
            }
            float temp1 = (float)marknum / listmarks.Count;
            if (temp1 == 5)
            {
                average = "Best";
            }

            else if (temp1 >= 4 && temp1 < 5)
            {
                average = "Better";
            }

            else if (temp1 >= 3 && temp1 < 4)
            {
                average = "Good";
            }

            else if (temp1 >= 2 && temp1 < 3)
            {
                average = "Moderate";
            }

            else if (temp1 >= 1 && temp1 < 2)
            {
                average = "Normal";
            }
            else if (temp1 < 1)
            {
                average = "Disqualified";
            }

        }
        return average + " by " + listmarks.Count.ToString() + " Staff";
    }

    #endregion

    /// <summary>
    /// Get Posting
    /// </summary>
    /// <returns>DataSet</returns>
    public DataSet GetPostingByPostingID(long postingID)
    {
        DataSet ds = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter(SQL_SELECT_POSTING_BY_ID, conn);
        adapter.SelectCommand.Parameters.AddWithValue("@postingID", postingID);
        adapter.Fill(ds);
        return ds;
    }

    /// <summary>
    /// Get Total Submisstions
    /// </summary>
    /// <returns>int</returns>
    public int GetTotalSubmissions(int competitionID)
    {
        int totalsubmissons = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_COUNT_POSTING_BY_COMPETITIONID, conn);
            cmd.Parameters.Add("@compID", competitionID);
            if (Connect())
            {
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    totalsubmissons = reader.GetInt32(0);
                    break;
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return totalsubmissons;
    }

}